{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "#显示所有列\n",
    "pd.set_option('display.max_columns', None)\n",
    "#显示所有行\n",
    "#pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.float_format', lambda x: '%.2f' % x)\n",
    "import datetime "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx_df = pd.read_table(\"./data/TXN_DATA.TXT\",sep = \",\",\n",
    "    names =[\"ID\",\"MccCode\",\"SIGNFLAG\",\"TxAmt\",\"sTxAmt\",\"TcCode\",\"No\",\"TxDate\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#trainset = pd.read_table(\"./data/m0_200411.txt\",sep = \",\")\n",
    "testset =pd.read_table(\"./data/m0_200412.txt\",sep = \",\")\n",
    "trainset = testset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(63532, 32)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trainset.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "add() takes 3 positional arguments but 4 were given",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-8-208fe5eddc45>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      6\u001b[0m \u001b[1;31m#pie = Pie(\"性别分布\")\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[0mpie\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mPie\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 8\u001b[1;33m \u001b[0mpie\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0madd\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mattr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mv1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      9\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m \u001b[0mpie\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: add() takes 3 positional arguments but 4 were given"
     ]
    }
   ],
   "source": [
    "#查看男女性占比\n",
    "from pyecharts.charts import Pie\n",
    "\n",
    "attr = [\"男性\",\"女性\"]\n",
    "v1 = [trainset[\"SEX\"].value_counts()[1],trainset[\"SEX\"].value_counts()[2]]\n",
    "#pie = Pie(\"性别分布\")\n",
    "pie = Pie()\n",
    "pie.add(\"\", attr, v1,)\n",
    "\n",
    "pie\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>\n",
       "    require.config({\n",
       "         paths:{\n",
       "           'echarts': '/nbextensions/echarts/echarts.min'\n",
       "         }\n",
       "    });\n",
       "</script>\n",
       "<div id=\"0a1de075a62343499f91fc678185d66a\" style=\"width:800px; height:400px;\"></div>\n",
       "\n",
       "<script>\n",
       "    require([ 'echarts' ],function(ec){\n",
       "\tvar myChart = ec.init(document.getElementById('0a1de075a62343499f91fc678185d66a'));\n",
       "var option =  {\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u6559\\u80b2\\u5206\\u5e03\",\n",
       "            \"subtext\": \"\",\n",
       "            \"left\": \"auto\",\n",
       "            \"top\": \"auto\",\n",
       "            \"textStyle\": {\n",
       "                \"color\": \"#000\",\n",
       "                \"fontSize\": 18\n",
       "            },\n",
       "            \"subtextStyle\": {\n",
       "                \"color\": \"#aaa\",\n",
       "                \"fontSize\": 12\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"toolbox\": {\n",
       "        \"show\": true,\n",
       "        \"orient\": \"vertical\",\n",
       "        \"left\": \"95%\",\n",
       "        \"top\": \"center\",\n",
       "        \"feature\": {\n",
       "            \"saveAsImage\": {\n",
       "                \"show\": true,\n",
       "                \"title\": \"\\u4e0b\\u8f7d\\u56fe\\u7247\"\n",
       "            },\n",
       "            \"restore\": {\n",
       "                \"show\": true\n",
       "            },\n",
       "            \"dataView\": {\n",
       "                \"show\": true\n",
       "            }\n",
       "        }\n",
       "    },\n",
       "    \"series_id\": 8925622,\n",
       "    \"tooltip\": {\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"formatter\": null,\n",
       "        \"textStyle\": {\n",
       "            \"color\": \"#fff\",\n",
       "            \"fontSize\": 14\n",
       "        }\n",
       "    },\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"pie\",\n",
       "            \"name\": \"\",\n",
       "            \"data\": [\n",
       "                {\n",
       "                    \"name\": \"\\u7855\\u535a\\u53ca\\u4ee5\\u4e0a\",\n",
       "                    \"value\": 27.0\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u672c\\u79d1\",\n",
       "                    \"value\": 22232.0\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u9ad8\\u4e2d\",\n",
       "                    \"value\": 27077.0\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u521d\\u4e2d\\u53ca\\u4ee5\\u4e0b\",\n",
       "                    \"value\": 12973.0\n",
       "                }\n",
       "            ],\n",
       "            \"radius\": [\n",
       "                \"0%\",\n",
       "                \"75%\"\n",
       "            ],\n",
       "            \"center\": [\n",
       "                \"50%\",\n",
       "                \"50%\"\n",
       "            ],\n",
       "            \"roseType\": null,\n",
       "            \"label\": {\n",
       "                \"normal\": {\n",
       "                    \"show\": true,\n",
       "                    \"position\": \"outside\",\n",
       "                    \"textStyle\": {\n",
       "                        \"color\": \"#000\",\n",
       "                        \"fontSize\": 12\n",
       "                    },\n",
       "                    \"formatter\": \"{b}: {d}%\"\n",
       "                },\n",
       "                \"emphasis\": {\n",
       "                    \"show\": true,\n",
       "                    \"position\": null,\n",
       "                    \"textStyle\": {\n",
       "                        \"color\": \"#fff\",\n",
       "                        \"fontSize\": 12\n",
       "                    }\n",
       "                }\n",
       "            },\n",
       "            \"seriesId\": 8925622\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u7855\\u535a\\u53ca\\u4ee5\\u4e0a\",\n",
       "                \"\\u672c\\u79d1\",\n",
       "                \"\\u9ad8\\u4e2d\",\n",
       "                \"\\u521d\\u4e2d\\u53ca\\u4ee5\\u4e0b\"\n",
       "            ],\n",
       "            \"selectedMode\": \"multiple\",\n",
       "            \"show\": true,\n",
       "            \"left\": \"center\",\n",
       "            \"top\": \"top\",\n",
       "            \"orient\": \"horizontal\",\n",
       "            \"textStyle\": {\n",
       "                \"fontSize\": 12,\n",
       "                \"color\": \"#333\"\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"backgroundColor\": \"#fff\",\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\",\n",
       "        \"#f6f5ec\"\n",
       "    ]\n",
       "};\n",
       "myChart.setOption(option);\n",
       "\n",
       "    });\n",
       "</script>\n"
      ],
      "text/plain": [
       "<pyecharts.charts.pie.Pie at 0x1003f908>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看教育占比\n",
    "from pyecharts import Pie\n",
    "\n",
    "attr = [\"硕博及以上\",\"本科\",\"高中\",\"初中及以下\"]\n",
    "v1 = [trainset[\"EDUCATION\"].value_counts()[-1],\n",
    "      trainset[\"EDUCATION\"].value_counts()[1],\n",
    "      trainset[\"EDUCATION\"].value_counts()[0],\n",
    "      sum(trainset[\"EDUCATION\"].value_counts()[2:7])]\n",
    "pie = Pie(\"教育分布\")\n",
    "pie.add(\"\", attr, v1, is_label_show=True)\n",
    "\n",
    "pie\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>\n",
       "    require.config({\n",
       "         paths:{\n",
       "           'echarts': '/nbextensions/echarts/echarts.min'\n",
       "         }\n",
       "    });\n",
       "</script>\n",
       "<div id=\"246f692fcaed49d7b28df1b6e0c20ae3\" style=\"width:800px; height:400px;\"></div>\n",
       "\n",
       "<script>\n",
       "    require([ 'echarts' ],function(ec){\n",
       "\tvar myChart = ec.init(document.getElementById('246f692fcaed49d7b28df1b6e0c20ae3'));\n",
       "var option =  {\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u5a5a\\u59fb\\u5206\\u5e03\",\n",
       "            \"subtext\": \"\",\n",
       "            \"left\": \"auto\",\n",
       "            \"top\": \"auto\",\n",
       "            \"textStyle\": {\n",
       "                \"color\": \"#000\",\n",
       "                \"fontSize\": 18\n",
       "            },\n",
       "            \"subtextStyle\": {\n",
       "                \"color\": \"#aaa\",\n",
       "                \"fontSize\": 12\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"toolbox\": {\n",
       "        \"show\": true,\n",
       "        \"orient\": \"vertical\",\n",
       "        \"left\": \"95%\",\n",
       "        \"top\": \"center\",\n",
       "        \"feature\": {\n",
       "            \"saveAsImage\": {\n",
       "                \"show\": true,\n",
       "                \"title\": \"\\u4e0b\\u8f7d\\u56fe\\u7247\"\n",
       "            },\n",
       "            \"restore\": {\n",
       "                \"show\": true\n",
       "            },\n",
       "            \"dataView\": {\n",
       "                \"show\": true\n",
       "            }\n",
       "        }\n",
       "    },\n",
       "    \"series_id\": 2608536,\n",
       "    \"tooltip\": {\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"formatter\": null,\n",
       "        \"textStyle\": {\n",
       "            \"color\": \"#fff\",\n",
       "            \"fontSize\": 14\n",
       "        }\n",
       "    },\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"pie\",\n",
       "            \"name\": \"\",\n",
       "            \"data\": [\n",
       "                {\n",
       "                    \"name\": \"\\u5df2\\u5a5a\",\n",
       "                    \"value\": 27141.0\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u672a\\u5a5a\",\n",
       "                    \"value\": 34291.0\n",
       "                },\n",
       "                {\n",
       "                    \"name\": \"\\u79bb\\u5a5a\",\n",
       "                    \"value\": 780.0\n",
       "                }\n",
       "            ],\n",
       "            \"radius\": [\n",
       "                \"0%\",\n",
       "                \"75%\"\n",
       "            ],\n",
       "            \"center\": [\n",
       "                \"50%\",\n",
       "                \"50%\"\n",
       "            ],\n",
       "            \"roseType\": null,\n",
       "            \"label\": {\n",
       "                \"normal\": {\n",
       "                    \"show\": true,\n",
       "                    \"position\": \"outside\",\n",
       "                    \"textStyle\": {\n",
       "                        \"color\": \"#000\",\n",
       "                        \"fontSize\": 12\n",
       "                    },\n",
       "                    \"formatter\": \"{b}: {d}%\"\n",
       "                },\n",
       "                \"emphasis\": {\n",
       "                    \"show\": true,\n",
       "                    \"position\": null,\n",
       "                    \"textStyle\": {\n",
       "                        \"color\": \"#fff\",\n",
       "                        \"fontSize\": 12\n",
       "                    }\n",
       "                }\n",
       "            },\n",
       "            \"seriesId\": 2608536\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u5df2\\u5a5a\",\n",
       "                \"\\u672a\\u5a5a\",\n",
       "                \"\\u79bb\\u5a5a\"\n",
       "            ],\n",
       "            \"selectedMode\": \"multiple\",\n",
       "            \"show\": true,\n",
       "            \"left\": \"center\",\n",
       "            \"top\": \"top\",\n",
       "            \"orient\": \"horizontal\",\n",
       "            \"textStyle\": {\n",
       "                \"fontSize\": 12,\n",
       "                \"color\": \"#333\"\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"backgroundColor\": \"#fff\",\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\",\n",
       "        \"#f6f5ec\"\n",
       "    ]\n",
       "};\n",
       "myChart.setOption(option);\n",
       "\n",
       "    });\n",
       "</script>\n"
      ],
      "text/plain": [
       "<pyecharts.charts.pie.Pie at 0xfe152e8>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看婚姻状态占比\n",
    "from pyecharts import Pie\n",
    "\n",
    "attr = [\"已婚\",\"未婚\",\"离婚\"]\n",
    "v1 = [trainset[\"MARRIAGE\"].value_counts()[1],\n",
    "      trainset[\"MARRIAGE\"].value_counts()[2],\n",
    "      trainset[\"MARRIAGE\"].value_counts()[3]]\n",
    "pie = Pie(\"婚姻分布\")\n",
    "pie.add(\"\", attr, v1, is_label_show=True)\n",
    "\n",
    "pie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#查看年龄分布\n",
    "import matplotlib.pyplot as plt\n",
    "plt.hist(trainset[\"AGE\"], rwidth=0.9)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#定义支付码填充函数\n",
    "def ex_str_pay_code(y):\n",
    "    e = list()\n",
    "    c = list()\n",
    "    n_3 = 0\n",
    "    n_24 = 0\n",
    "    for x in list(y):\n",
    "        if x in [\"z\",\"Z\",\"b\",\"B\"]:\n",
    "            e.append(\"0\")\n",
    "        elif x.isspace():\n",
    "            e.append(\"0\")\n",
    "        else:\n",
    "            e.append(x)\n",
    "            n_24 = n_24 + 1\n",
    "    for  i in range(1,9) :\n",
    "        n_3 = 0\n",
    "        for j in list(y)[4*(i-1):4*i-1]:\n",
    "            if j in ['2','3','4','5','6','7','8','9']:\n",
    "                n_3 = n_3 + 1\n",
    "        c.append(str(n_3))\n",
    "     #24位支付码   \n",
    "    pay_code = ''.join(e)\n",
    "    #每三个月逾期次数\n",
    "    overdue_cnt_3 = ''.join(c)\n",
    "    #最大逾期月份\n",
    "    max_overdue_mon = max(e)\n",
    "    return  [pay_code,overdue_cnt_3,max_overdue_mon]\n",
    "\n",
    "#调整函数\n",
    "def ex_repay_rate(x):\n",
    "    if x<=2 and x>=-2:\n",
    "        return x\n",
    "    elif x>2:\n",
    "        return 2\n",
    "    else:\n",
    "        return -2\n",
    "#实动月份\n",
    "def use_mon(x):\n",
    "    a = list()\n",
    "    for i in list(x):\n",
    "        if i not in (\"Z\",\"z\",\" \"):\n",
    "            a.append(i)\n",
    "    return len(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#定义星座\n",
    "def xingzuo(month, day):\n",
    "    day = int(month)*100 + int(day)\n",
    "    if day >= 123 and day <=218:\n",
    "        return('1:水瓶座')\n",
    "    elif day <= 320:\n",
    "        return('2:双鱼座')\n",
    "    elif day <= 419:\n",
    "        return('3:白羊座')\n",
    "    elif day <= 520:\n",
    "        return('4:金牛座')\n",
    "    elif day <= 621:\n",
    "        return('5:双子座')\n",
    "    elif day <= 722:\n",
    "        return('6:巨蟹座')\n",
    "    elif day <= 822:\n",
    "        return('7:狮子座')\n",
    "    elif day <= 922:\n",
    "        return('8:处女座')\n",
    "    elif day <= 1023:\n",
    "        return('9:天秤座')\n",
    "    elif day <= 1122:\n",
    "        return('10:天蝎座')\n",
    "    elif day <= 1221:\n",
    "        return('11:射手座')\n",
    "    else:\n",
    "        return('12:摩羯座')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#计算前6个月各月的额度使用率\n",
    "trainset[\"LIMIT_RATE_1\"]=[*map(lambda x,y:x/y,trainset[\"BILL_AMT1\"],trainset[\"LIMIT_BAL\"])]\n",
    "trainset[\"LIMIT_RATE_2\"]=[*map(lambda x,y:x/y,trainset[\"BILL_AMT2\"],trainset[\"LIMIT_BAL\"])]\n",
    "trainset[\"LIMIT_RATE_3\"]=[*map(lambda x,y:x/y,trainset[\"BILL_AMT3\"],trainset[\"LIMIT_BAL\"])]\n",
    "trainset[\"LIMIT_RATE_4\"]=[*map(lambda x,y:x/y,trainset[\"BILL_AMT4\"],trainset[\"LIMIT_BAL\"])]\n",
    "trainset[\"LIMIT_RATE_5\"]=[*map(lambda x,y:x/y,trainset[\"BILL_AMT5\"],trainset[\"LIMIT_BAL\"])]\n",
    "trainset[\"LIMIT_RATE_6\"]=[*map(lambda x,y:x/y,trainset[\"BILL_AMT6\"],trainset[\"LIMIT_BAL\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#计算前6个月各月的还贷比\n",
    "trainset[\"REPAY_RATE_1\"]=[*map(lambda x,y:ex_repay_rate(x/y) if y!=0 else 0,trainset[\"PAY_AMT1\"],trainset[\"BILL_AMT1\"])]\n",
    "trainset[\"REPAY_RATE_2\"]=[*map(lambda x,y:ex_repay_rate(x/y) if y!=0 else 0,trainset[\"PAY_AMT2\"],trainset[\"BILL_AMT2\"])]\n",
    "trainset[\"REPAY_RATE_3\"]=[*map(lambda x,y:ex_repay_rate(x/y) if y!=0 else 0,trainset[\"PAY_AMT3\"],trainset[\"BILL_AMT3\"])]\n",
    "trainset[\"REPAY_RATE_4\"]=[*map(lambda x,y:ex_repay_rate(x/y) if y!=0 else 0,trainset[\"PAY_AMT4\"],trainset[\"BILL_AMT4\"])]\n",
    "trainset[\"REPAY_RATE_5\"]=[*map(lambda x,y:ex_repay_rate(x/y) if y!=0 else 0,trainset[\"PAY_AMT5\"],trainset[\"BILL_AMT5\"])]\n",
    "trainset[\"REPAY_RATE_6\"]=[*map(lambda x,y:ex_repay_rate(x/y) if y!=0 else 0,trainset[\"PAY_AMT6\"],trainset[\"BILL_AMT6\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#求得支付码对应的位数，求得用户使用月份\n",
    "trainset['ALL_MON'] = [*map(lambda x:int(len(x.split(' ')[0])),trainset[\"PAY_COND\"])]\n",
    "#将这个数据填充为24位，Z\\0\\B均替换为0，不满24位的右边补0\n",
    "#每三个月的逾期次数\n",
    "trainset['OVERDUE_CNT_3'] = [*map(lambda x: int(ex_str_pay_code(x)[1]),trainset[\"PAY_COND\"])]\n",
    "#最大逾期月份\n",
    "trainset['OVERDUE_MAX_MON'] = [*map(lambda x: int(ex_str_pay_code(x)[2]),trainset[\"PAY_COND\"])]\n",
    "#获取实动月份\n",
    "trainset[\"USE_MON\"] = [*map(lambda x:use_mon(x),trainset[\"PAY_COND\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ID_NO_IDENT', 'LIMIT_BAL', 'SEX', 'EDUCATION', 'JOB_TYPE', 'MARRIAGE',\n",
       "       'ZIP_CODE', 'BIRTHDAY', 'AGE', 'CASE_NO', 'CASE_FROM', 'PAY_COND',\n",
       "       'PAY_1', 'PAY_2', 'PAY_3', 'PAY_4', 'PAY_5', 'PAY_6', 'BILL_AMT1',\n",
       "       'BILL_AMT2', 'BILL_AMT3', 'BILL_AMT4', 'BILL_AMT5', 'BILL_AMT6',\n",
       "       'PAY_AMT1', 'PAY_AMT2', 'PAY_AMT3', 'PAY_AMT4', 'PAY_AMT5', 'PAY_AMT6',\n",
       "       'SCORING', 'CUST_TYPE', 'LIMIT_RATE_1', 'LIMIT_RATE_2', 'LIMIT_RATE_3',\n",
       "       'LIMIT_RATE_4', 'LIMIT_RATE_5', 'LIMIT_RATE_6', 'REPAY_RATE_1',\n",
       "       'REPAY_RATE_2', 'REPAY_RATE_3', 'REPAY_RATE_4', 'REPAY_RATE_5',\n",
       "       'REPAY_RATE_6', 'ALL_MON', 'OVERDUE_CNT_3', 'OVERDUE_MAX_MON',\n",
       "       'USE_MON'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trainset.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#画箱体图\n",
    "from matplotlib import pyplot as plt\n",
    "df=pd.DataFrame({\"Good\":trainset[\"ALL_MON\"][trainset[\"CUST_TYPE\"]==\"G\"],\n",
    "                 \"Bad\":trainset[\"ALL_MON\"][trainset[\"CUST_TYPE\"]==\"B\"]})\n",
    "df.boxplot()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID_NO_IDENT</th>\n",
       "      <th>LIMIT_BAL</th>\n",
       "      <th>SEX</th>\n",
       "      <th>MARRIAGE</th>\n",
       "      <th>ZIP_CODE</th>\n",
       "      <th>BIRTHDAY</th>\n",
       "      <th>AGE</th>\n",
       "      <th>CASE_NO</th>\n",
       "      <th>CASE_FROM</th>\n",
       "      <th>BILL_AMT1</th>\n",
       "      <th>BILL_AMT2</th>\n",
       "      <th>BILL_AMT3</th>\n",
       "      <th>BILL_AMT4</th>\n",
       "      <th>BILL_AMT5</th>\n",
       "      <th>BILL_AMT6</th>\n",
       "      <th>PAY_AMT1</th>\n",
       "      <th>PAY_AMT2</th>\n",
       "      <th>PAY_AMT3</th>\n",
       "      <th>PAY_AMT4</th>\n",
       "      <th>PAY_AMT5</th>\n",
       "      <th>PAY_AMT6</th>\n",
       "      <th>SCORING</th>\n",
       "      <th>LIMIT_RATE_1</th>\n",
       "      <th>LIMIT_RATE_2</th>\n",
       "      <th>LIMIT_RATE_3</th>\n",
       "      <th>LIMIT_RATE_4</th>\n",
       "      <th>LIMIT_RATE_5</th>\n",
       "      <th>LIMIT_RATE_6</th>\n",
       "      <th>REPAY_RATE_1</th>\n",
       "      <th>REPAY_RATE_2</th>\n",
       "      <th>REPAY_RATE_3</th>\n",
       "      <th>REPAY_RATE_4</th>\n",
       "      <th>REPAY_RATE_5</th>\n",
       "      <th>REPAY_RATE_6</th>\n",
       "      <th>ALL_MON</th>\n",
       "      <th>OVERDUE_CNT_3</th>\n",
       "      <th>OVERDUE_MAX_MON</th>\n",
       "      <th>USE_MON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>56244.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "      <td>63532.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1102508.73</td>\n",
       "      <td>168976.23</td>\n",
       "      <td>1.59</td>\n",
       "      <td>1.57</td>\n",
       "      <td>417.50</td>\n",
       "      <td>19697042.64</td>\n",
       "      <td>35.36</td>\n",
       "      <td>8555220513732.71</td>\n",
       "      <td>6.12</td>\n",
       "      <td>49579.40</td>\n",
       "      <td>47401.54</td>\n",
       "      <td>45270.61</td>\n",
       "      <td>42543.72</td>\n",
       "      <td>38145.76</td>\n",
       "      <td>36843.86</td>\n",
       "      <td>5782.91</td>\n",
       "      <td>5691.60</td>\n",
       "      <td>5625.93</td>\n",
       "      <td>4785.55</td>\n",
       "      <td>4583.67</td>\n",
       "      <td>4710.74</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.37</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.29</td>\n",
       "      <td>0.29</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.25</td>\n",
       "      <td>18.95</td>\n",
       "      <td>3879578.20</td>\n",
       "      <td>1.30</td>\n",
       "      <td>14.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>644080.94</td>\n",
       "      <td>130822.89</td>\n",
       "      <td>0.49</td>\n",
       "      <td>0.52</td>\n",
       "      <td>265.68</td>\n",
       "      <td>92951.25</td>\n",
       "      <td>9.30</td>\n",
       "      <td>1898587086426.05</td>\n",
       "      <td>4.80</td>\n",
       "      <td>73549.02</td>\n",
       "      <td>71629.17</td>\n",
       "      <td>69351.02</td>\n",
       "      <td>65872.24</td>\n",
       "      <td>60774.63</td>\n",
       "      <td>59232.40</td>\n",
       "      <td>17990.24</td>\n",
       "      <td>18118.39</td>\n",
       "      <td>18219.23</td>\n",
       "      <td>17510.83</td>\n",
       "      <td>15152.59</td>\n",
       "      <td>16093.03</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.38</td>\n",
       "      <td>0.35</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.61</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.56</td>\n",
       "      <td>0.56</td>\n",
       "      <td>0.58</td>\n",
       "      <td>6.66</td>\n",
       "      <td>8465012.46</td>\n",
       "      <td>0.95</td>\n",
       "      <td>8.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>7.00</td>\n",
       "      <td>10000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>19260318.00</td>\n",
       "      <td>21.00</td>\n",
       "      <td>4072100004.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>-154973.00</td>\n",
       "      <td>-166648.00</td>\n",
       "      <td>-179019.00</td>\n",
       "      <td>-182415.00</td>\n",
       "      <td>-186386.00</td>\n",
       "      <td>-209051.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>-1.02</td>\n",
       "      <td>-1.45</td>\n",
       "      <td>-1.40</td>\n",
       "      <td>-1.37</td>\n",
       "      <td>-1.18</td>\n",
       "      <td>-1.03</td>\n",
       "      <td>-2.00</td>\n",
       "      <td>-2.00</td>\n",
       "      <td>-2.00</td>\n",
       "      <td>-2.00</td>\n",
       "      <td>-2.00</td>\n",
       "      <td>-2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>542313.25</td>\n",
       "      <td>50000.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>231.00</td>\n",
       "      <td>19640306.00</td>\n",
       "      <td>28.00</td>\n",
       "      <td>9008132808287.50</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2550.00</td>\n",
       "      <td>1792.00</td>\n",
       "      <td>1275.50</td>\n",
       "      <td>853.75</td>\n",
       "      <td>626.75</td>\n",
       "      <td>396.00</td>\n",
       "      <td>482.75</td>\n",
       "      <td>380.00</td>\n",
       "      <td>41.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>14.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1091100.00</td>\n",
       "      <td>150000.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>330.00</td>\n",
       "      <td>19711003.00</td>\n",
       "      <td>34.00</td>\n",
       "      <td>9110302409158.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>19607.50</td>\n",
       "      <td>18626.00</td>\n",
       "      <td>18087.50</td>\n",
       "      <td>17131.50</td>\n",
       "      <td>15011.00</td>\n",
       "      <td>13693.50</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>1880.00</td>\n",
       "      <td>1242.00</td>\n",
       "      <td>1200.00</td>\n",
       "      <td>1290.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.14</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.04</td>\n",
       "      <td>23.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>16.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1662860.00</td>\n",
       "      <td>240000.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>702.00</td>\n",
       "      <td>19770726.00</td>\n",
       "      <td>41.00</td>\n",
       "      <td>9207243722171.50</td>\n",
       "      <td>11.00</td>\n",
       "      <td>63541.25</td>\n",
       "      <td>60169.00</td>\n",
       "      <td>57153.75</td>\n",
       "      <td>52601.25</td>\n",
       "      <td>47832.50</td>\n",
       "      <td>46514.25</td>\n",
       "      <td>5000.00</td>\n",
       "      <td>5000.00</td>\n",
       "      <td>5000.00</td>\n",
       "      <td>4000.00</td>\n",
       "      <td>4000.00</td>\n",
       "      <td>4000.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>0.82</td>\n",
       "      <td>0.79</td>\n",
       "      <td>0.75</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.55</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.13</td>\n",
       "      <td>24.00</td>\n",
       "      <td>1000000.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>24.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2205879.00</td>\n",
       "      <td>2000000.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>983.00</td>\n",
       "      <td>19841109.00</td>\n",
       "      <td>79.00</td>\n",
       "      <td>9306155922001.00</td>\n",
       "      <td>21.00</td>\n",
       "      <td>947995.00</td>\n",
       "      <td>1001477.00</td>\n",
       "      <td>991631.00</td>\n",
       "      <td>706864.00</td>\n",
       "      <td>904519.00</td>\n",
       "      <td>927171.00</td>\n",
       "      <td>1002035.00</td>\n",
       "      <td>1227082.00</td>\n",
       "      <td>889043.00</td>\n",
       "      <td>904549.00</td>\n",
       "      <td>559272.00</td>\n",
       "      <td>942817.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>4.38</td>\n",
       "      <td>4.49</td>\n",
       "      <td>10.69</td>\n",
       "      <td>4.74</td>\n",
       "      <td>4.94</td>\n",
       "      <td>4.26</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>24.00</td>\n",
       "      <td>33333300.00</td>\n",
       "      <td>9.00</td>\n",
       "      <td>24.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID_NO_IDENT  LIMIT_BAL      SEX  MARRIAGE  ZIP_CODE    BIRTHDAY  \\\n",
       "count     63532.00   63532.00 63532.00  63532.00  63532.00    63532.00   \n",
       "mean    1102508.73  168976.23     1.59      1.57    417.50 19697042.64   \n",
       "std      644080.94  130822.89     0.49      0.52    265.68    92951.25   \n",
       "min           7.00   10000.00     1.00      0.00      0.00 19260318.00   \n",
       "25%      542313.25   50000.00     1.00      1.00    231.00 19640306.00   \n",
       "50%     1091100.00  150000.00     2.00      2.00    330.00 19711003.00   \n",
       "75%     1662860.00  240000.00     2.00      2.00    702.00 19770726.00   \n",
       "max     2205879.00 2000000.00     2.00      3.00    983.00 19841109.00   \n",
       "\n",
       "           AGE          CASE_NO  CASE_FROM  BILL_AMT1  BILL_AMT2  BILL_AMT3  \\\n",
       "count 63532.00         56244.00   63532.00   63532.00   63532.00   63532.00   \n",
       "mean     35.36 8555220513732.71       6.12   49579.40   47401.54   45270.61   \n",
       "std       9.30 1898587086426.05       4.80   73549.02   71629.17   69351.02   \n",
       "min      21.00    4072100004.00       1.00 -154973.00 -166648.00 -179019.00   \n",
       "25%      28.00 9008132808287.50       2.00    2550.00    1792.00    1275.50   \n",
       "50%      34.00 9110302409158.00       5.00   19607.50   18626.00   18087.50   \n",
       "75%      41.00 9207243722171.50      11.00   63541.25   60169.00   57153.75   \n",
       "max      79.00 9306155922001.00      21.00  947995.00 1001477.00  991631.00   \n",
       "\n",
       "       BILL_AMT4  BILL_AMT5  BILL_AMT6   PAY_AMT1   PAY_AMT2  PAY_AMT3  \\\n",
       "count   63532.00   63532.00   63532.00   63532.00   63532.00  63532.00   \n",
       "mean    42543.72   38145.76   36843.86    5782.91    5691.60   5625.93   \n",
       "std     65872.24   60774.63   59232.40   17990.24   18118.39  18219.23   \n",
       "min   -182415.00 -186386.00 -209051.00       0.00       0.00      0.00   \n",
       "25%       853.75     626.75     396.00     482.75     380.00     41.00   \n",
       "50%     17131.50   15011.00   13693.50    2000.00    2000.00   1880.00   \n",
       "75%     52601.25   47832.50   46514.25    5000.00    5000.00   5000.00   \n",
       "max    706864.00  904519.00  927171.00 1002035.00 1227082.00 889043.00   \n",
       "\n",
       "       PAY_AMT4  PAY_AMT5  PAY_AMT6  SCORING  LIMIT_RATE_1  LIMIT_RATE_2  \\\n",
       "count  63532.00  63532.00  63532.00     0.00      63532.00      63532.00   \n",
       "mean    4785.55   4583.67   4710.74      nan          0.41          0.39   \n",
       "std    17510.83  15152.59  16093.03      nan          0.41          0.41   \n",
       "min        0.00      0.00      0.00      nan         -1.02         -1.45   \n",
       "25%        0.00      0.00      0.00      nan          0.02          0.01   \n",
       "50%     1242.00   1200.00   1290.00      nan          0.27          0.24   \n",
       "75%     4000.00   4000.00   4000.00      nan          0.82          0.79   \n",
       "max   904549.00 559272.00 942817.00      nan          4.38          4.49   \n",
       "\n",
       "       LIMIT_RATE_3  LIMIT_RATE_4  LIMIT_RATE_5  LIMIT_RATE_6  REPAY_RATE_1  \\\n",
       "count      63532.00      63532.00      63532.00      63532.00      63532.00   \n",
       "mean           0.37          0.34          0.30          0.29          0.29   \n",
       "std            0.40          0.38          0.35          0.34          0.60   \n",
       "min           -1.40         -1.37         -1.18         -1.03         -2.00   \n",
       "25%            0.01          0.01          0.00          0.00          0.02   \n",
       "50%            0.20          0.17          0.14          0.12          0.05   \n",
       "75%            0.75          0.67          0.57          0.55          0.22   \n",
       "max           10.69          4.74          4.94          4.26          2.00   \n",
       "\n",
       "       REPAY_RATE_2  REPAY_RATE_3  REPAY_RATE_4  REPAY_RATE_5  REPAY_RATE_6  \\\n",
       "count      63532.00      63532.00      63532.00      63532.00      63532.00   \n",
       "mean           0.28          0.27          0.23          0.24          0.25   \n",
       "std            0.61          0.60          0.56          0.56          0.58   \n",
       "min           -2.00         -2.00         -2.00         -2.00         -2.00   \n",
       "25%            0.00          0.00          0.00          0.00          0.00   \n",
       "50%            0.05          0.04          0.04          0.04          0.04   \n",
       "75%            0.20          0.17          0.11          0.11          0.13   \n",
       "max            2.00          2.00          2.00          2.00          2.00   \n",
       "\n",
       "       ALL_MON  OVERDUE_CNT_3  OVERDUE_MAX_MON  USE_MON  \n",
       "count 63532.00       63532.00         63532.00 63532.00  \n",
       "mean     18.95     3879578.20             1.30    14.71  \n",
       "std       6.66     8465012.46             0.95     8.34  \n",
       "min       0.00           0.00             0.00     0.00  \n",
       "25%      14.00           0.00             1.00     8.00  \n",
       "50%      23.00           0.00             1.00    16.00  \n",
       "75%      24.00     1000000.00             2.00    24.00  \n",
       "max      24.00    33333300.00             9.00    24.00  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trainset.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-14-448e9296a472>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mtx_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdescribe\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mdescribe\u001b[1;34m(self, percentiles, include, exclude)\u001b[0m\n\u001b[0;32m   8568\u001b[0m             \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mselect_dtypes\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minclude\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0minclude\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexclude\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexclude\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   8569\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 8570\u001b[1;33m         \u001b[0mldesc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mdescribe_1d\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0m_\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0ms\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0miteritems\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   8571\u001b[0m         \u001b[1;31m# set a convenient order for rows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   8572\u001b[0m         \u001b[0mnames\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m<listcomp>\u001b[1;34m(.0)\u001b[0m\n\u001b[0;32m   8568\u001b[0m             \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mselect_dtypes\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minclude\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0minclude\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexclude\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexclude\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   8569\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 8570\u001b[1;33m         \u001b[0mldesc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mdescribe_1d\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0ms\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0m_\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0ms\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0miteritems\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   8571\u001b[0m         \u001b[1;31m# set a convenient order for rows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   8572\u001b[0m         \u001b[0mnames\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mdescribe_1d\u001b[1;34m(data)\u001b[0m\n\u001b[0;32m   8547\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mdescribe_categorical_1d\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   8548\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[0mis_numeric_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 8549\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mdescribe_numeric_1d\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   8550\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[0mis_timedelta64_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   8551\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mdescribe_numeric_1d\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mdescribe_numeric_1d\u001b[1;34m(series)\u001b[0m\n\u001b[0;32m   8520\u001b[0m                           formatted_percentiles + ['max'])\n\u001b[0;32m   8521\u001b[0m             d = ([series.count(), series.mean(), series.std(), series.min()] +\n\u001b[1;32m-> 8522\u001b[1;33m                  [series.quantile(x) for x in percentiles] + [series.max()])\n\u001b[0m\u001b[0;32m   8523\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mSeries\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0md\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstat_index\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mseries\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   8524\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36mcount\u001b[1;34m(self, level)\u001b[0m\n\u001b[0;32m   1414\u001b[0m         \"\"\"\n\u001b[0;32m   1415\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mlevel\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1416\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mnotna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcom\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_values_from_object\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msum\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1417\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1418\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlevel\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcompat\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstring_types\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\dtypes\\missing.py\u001b[0m in \u001b[0;36mnotna\u001b[1;34m(obj)\u001b[0m\n\u001b[0;32m    331\u001b[0m     \u001b[0mName\u001b[0m\u001b[1;33m:\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mbool\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    332\u001b[0m     \"\"\"\n\u001b[1;32m--> 333\u001b[1;33m     \u001b[0mres\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0misna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    334\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mis_scalar\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    335\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mres\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\dtypes\\missing.py\u001b[0m in \u001b[0;36misna\u001b[1;34m(obj)\u001b[0m\n\u001b[0;32m    104\u001b[0m     \u001b[0mName\u001b[0m\u001b[1;33m:\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mbool\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    105\u001b[0m     \"\"\"\n\u001b[1;32m--> 106\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0m_isna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    107\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    108\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\dtypes\\missing.py\u001b[0m in \u001b[0;36m_isna_new\u001b[1;34m(obj)\u001b[0m\n\u001b[0;32m    118\u001b[0m     elif isinstance(obj, (ABCSeries, np.ndarray, ABCIndexClass,\n\u001b[0;32m    119\u001b[0m                           ABCExtensionArray)):\n\u001b[1;32m--> 120\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0m_isna_ndarraylike\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    121\u001b[0m     \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mABCGeneric\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    122\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mobj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_constructor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0misna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfunc\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0misna\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\dtypes\\missing.py\u001b[0m in \u001b[0;36m_isna_ndarraylike\u001b[1;34m(obj)\u001b[0m\n\u001b[0;32m    218\u001b[0m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mvalues\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mview\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'i8'\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0miNaT\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    219\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 220\u001b[1;33m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0misnan\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    221\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    222\u001b[0m     \u001b[1;31m# box\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "tx_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>MccCode</th>\n",
       "      <th>SIGNFLAG</th>\n",
       "      <th>TxAmt</th>\n",
       "      <th>sTxAmt</th>\n",
       "      <th>TcCode</th>\n",
       "      <th>No</th>\n",
       "      <th>TxDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2130081.00</td>\n",
       "      <td>5311</td>\n",
       "      <td>+</td>\n",
       "      <td>6.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>40</td>\n",
       "      <td>4579530401421903</td>\n",
       "      <td>20040510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>843083.00</td>\n",
       "      <td>5411</td>\n",
       "      <td>+</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>40</td>\n",
       "      <td>4579522401255605</td>\n",
       "      <td>20040522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1432605.00</td>\n",
       "      <td>5968</td>\n",
       "      <td>+</td>\n",
       "      <td>51.81</td>\n",
       "      <td>51.81</td>\n",
       "      <td>40</td>\n",
       "      <td>4514453605964206</td>\n",
       "      <td>20040919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21431.00</td>\n",
       "      <td>5311</td>\n",
       "      <td>+</td>\n",
       "      <td>30.60</td>\n",
       "      <td>30.60</td>\n",
       "      <td>40</td>\n",
       "      <td>4579532902001507</td>\n",
       "      <td>20040507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>166293.00</td>\n",
       "      <td>4722</td>\n",
       "      <td>+</td>\n",
       "      <td>97.92</td>\n",
       "      <td>97.92</td>\n",
       "      <td>40</td>\n",
       "      <td>4579531600090903</td>\n",
       "      <td>20040624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1789252.00</td>\n",
       "      <td>5541</td>\n",
       "      <td>+</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.70</td>\n",
       "      <td>40</td>\n",
       "      <td>4579522002578900</td>\n",
       "      <td>20040815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2035955.00</td>\n",
       "      <td>0</td>\n",
       "      <td>-</td>\n",
       "      <td>3.99</td>\n",
       "      <td>-3.99</td>\n",
       "      <td>43</td>\n",
       "      <td>4514453610086805</td>\n",
       "      <td>20040729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>949283.00</td>\n",
       "      <td>7011</td>\n",
       "      <td>+</td>\n",
       "      <td>33.60</td>\n",
       "      <td>33.60</td>\n",
       "      <td>40</td>\n",
       "      <td>5179510000220600</td>\n",
       "      <td>20040704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2201572.00</td>\n",
       "      <td>6010</td>\n",
       "      <td>+</td>\n",
       "      <td>150.00</td>\n",
       "      <td>150.00</td>\n",
       "      <td>30</td>\n",
       "      <td>4579532409524001</td>\n",
       "      <td>20041122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1105981.00</td>\n",
       "      <td>5311</td>\n",
       "      <td>+</td>\n",
       "      <td>8.99</td>\n",
       "      <td>8.99</td>\n",
       "      <td>40</td>\n",
       "      <td>4579522502861509</td>\n",
       "      <td>20041111</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID  MccCode SIGNFLAG  TxAmt  sTxAmt  TcCode                No  \\\n",
       "0 2130081.00     5311        +   6.00    6.00      40  4579530401421903   \n",
       "1  843083.00     5411        +   3.00    3.00      40  4579522401255605   \n",
       "2 1432605.00     5968        +  51.81   51.81      40  4514453605964206   \n",
       "3   21431.00     5311        +  30.60   30.60      40  4579532902001507   \n",
       "4  166293.00     4722        +  97.92   97.92      40  4579531600090903   \n",
       "5 1789252.00     5541        +   0.70    0.70      40  4579522002578900   \n",
       "6 2035955.00        0        -   3.99   -3.99      43  4514453610086805   \n",
       "7  949283.00     7011        +  33.60   33.60      40  5179510000220600   \n",
       "8 2201572.00     6010        + 150.00  150.00      30  4579532409524001   \n",
       "9 1105981.00     5311        +   8.99    8.99      40  4579522502861509   \n",
       "\n",
       "     TxDate  \n",
       "0  20040510  \n",
       "1  20040522  \n",
       "2  20040919  \n",
       "3  20040507  \n",
       "4  20040624  \n",
       "5  20040815  \n",
       "6  20040729  \n",
       "7  20040704  \n",
       "8  20041122  \n",
       "9  20041111  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#处理交易数据\n",
    "tx_df[\"TxAmt\"]=tx_df[\"TxAmt\"]/100\n",
    "tx_df[\"sTxAmt\"]=tx_df[\"sTxAmt\"]/100\n",
    "tx_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>MccCode</th>\n",
       "      <th>TxAmt</th>\n",
       "      <th>sTxAmt</th>\n",
       "      <th>TcCode</th>\n",
       "      <th>No</th>\n",
       "      <th>TxDate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SIGNFLAG</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>+</th>\n",
       "      <td>16984892</td>\n",
       "      <td>16985500</td>\n",
       "      <td>16985500</td>\n",
       "      <td>16985500</td>\n",
       "      <td>16985500</td>\n",
       "      <td>16985500</td>\n",
       "      <td>16985500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-</th>\n",
       "      <td>1882547</td>\n",
       "      <td>1882579</td>\n",
       "      <td>1882579</td>\n",
       "      <td>1882579</td>\n",
       "      <td>1882579</td>\n",
       "      <td>1882579</td>\n",
       "      <td>1882579</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID   MccCode     TxAmt    sTxAmt    TcCode        No    TxDate\n",
       "SIGNFLAG                                                                      \n",
       "+         16984892  16985500  16985500  16985500  16985500  16985500  16985500\n",
       "-          1882547   1882579   1882579   1882579   1882579   1882579   1882579"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_df.groupby(\"SIGNFLAG\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获取各个ID的消费次数和消费金额\n",
    "txset=tx_df[tx_df[\"SIGNFLAG\"]==\"+\"]\n",
    "ex_cnt = txset.groupby(\"ID\")[\"ID\"].size()\n",
    "ex_cnt.name = 'Count'\n",
    "#金额\n",
    "ex_amt = txset.groupby(\"ID\")[\"TxAmt\"].sum()\n",
    "ex_amt.name = \"Amount\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获取各个ID的入账次数和入账金额\n",
    "txset1=tx_df[tx_df[\"SIGNFLAG\"]==\"-\"]\n",
    "ex_cnt1 = txset1.groupby(\"ID\")[\"ID\"].size()\n",
    "ex_cnt1.name = \"sCcount\"\n",
    "#金额\n",
    "ex_amt1 = txset1.groupby(\"ID\")[\"TxAmt\"].sum()\n",
    "ex_amt1.name = \"sAmount\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获取各个ID的交易月份数\n",
    "tx_df[\"Tx_Mon\"]=[*map(lambda x:str(x)[0:6],tx_df[\"TxDate\"])]\n",
    "tx_count = tx_df.groupby([\"ID\",\"Tx_Mon\"])[\"ID\"].count()\n",
    "tx_mon_cnt = tx_count.to_frame(name = \"A\").reset_index().groupby(\"ID\").count()[\"Tx_Mon\"]\n",
    "tx_mon_cnt.name = \"Tx_Mon\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#月均出账次数\n",
    "#月均出账金额\n",
    "#月均入账次数\n",
    "#出账笔均金额\n",
    "#交易月份数\n",
    "#资金需求\n",
    "#近3月出账次数\n",
    "#近3月出账金额\n",
    "#近3月入账次数\n",
    "#消费渠道数\n",
    "#高风险MCC码\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#读入MccCode字段\n",
    "MccCode = pd.read_csv(\"./Data/MccCode.csv\",sep = \",\")\n",
    "\n",
    "#加入Mcc码的交易中类\n",
    "tx_df1 = pd.merge(tx_df,MccCode,how = \"left\",left_on = \"MccCode\",right_on = \"MCC\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#对未匹配上的数据填充“空”\n",
    "tx_df1[\"中类\"] = tx_df1['中类'].fillna(\"空\")\n",
    "#将好坏客户与交易类型连接起来\n",
    "trainset1 = trainset[[\"ID_NO_IDENT\",\"CUST_TYPE\"]]\n",
    "tx_df_2 = tx_df1[[\"ID\",\"中类\"]]\n",
    "traindata = pd.merge(trainset1 ,tx_df_2,how = \"right\",left_on = \"ID_NO_IDENT\",right_on =\"ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.14006453034631008"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#求得好坏客户不同类别的交易数量\n",
    "left = traindata.groupby([\"CUST_TYPE\",\"中类\"])[\"ID\"].count().to_frame(name = \"lei_count\").reset_index()\n",
    "#求得不同类别的交易数量（不分好坏客户）\n",
    "right = left.groupby(\"中类\")[\"lei_count\"].sum().to_frame(name = \"count\").reset_index()\n",
    "#合并一起求占比\n",
    "traindata = pd.merge(left,right,how = \"outer\",left_on = \"中类\",right_on = \"中类\")\n",
    "#Mcc占比为好坏客户不同类别的交易占比\n",
    "traindata[\"Mcc_RATE\"] = traindata[\"lei_count\"]/traindata[\"count\"]\n",
    "#单独拿出坏客户查看\n",
    "traindata_bad = traindata[traindata[\"CUST_TYPE\"] ==\"B\"]\n",
    "#风险切分点为14%，故14%*0.85=12%以下为低风险，14%*1.15=16%以上为高风险，中间为中等风险\n",
    "traindata[traindata[\"CUST_TYPE\"] ==\"B\"][\"lei_count\"].sum()/(traindata[traindata[\"CUST_TYPE\"] ==\"B\"][\"lei_count\"].sum()+traindata[traindata[\"CUST_TYPE\"] ==\"G\"][\"lei_count\"].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#计算坏客户中Mcc码的占比，切分交易风险类别\n",
    "def Mcc_rate_bin(x):\n",
    "    if x>=0.16:\n",
    "        return \"高\"\n",
    "    elif x>=0.12:\n",
    "        return \"中\"\n",
    "    else:\n",
    "        return \"低\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'traindata_bad' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-21-473f9692f8e2>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m#获得高风险类别交易\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mtraindata_bad\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"Mcc_bin\"\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0mmap\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mx\u001b[0m \u001b[1;33m:\u001b[0m \u001b[0mMcc_rate_bin\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mx\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mtraindata_bad\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"Mcc_RATE\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'traindata_bad' is not defined"
     ]
    }
   ],
   "source": [
    "#获得高风险类别交易\n",
    "traindata_bad[\"Mcc_bin\"] = [*map(lambda x : Mcc_rate_bin(x),traindata_bad[\"Mcc_RATE\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'traindata_bad' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-20-3e6aff1020a9>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m#获得风险交易字典值\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mrisk_dict\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mtraindata_bad\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"中类\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m\"Mcc_bin\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'traindata_bad' is not defined"
     ]
    }
   ],
   "source": [
    "#获得风险交易字典值\n",
    "risk_dict = traindata_bad[[\"中类\",\"Mcc_bin\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#-------算出每一个客户中高风险交易占比--------\n",
    "\n",
    "#将交易数据与风险字典进行匹配\n",
    "tx_df2 = pd.merge(tx_df1,risk_dict,how = \"left\",left_on = \"中类\",right_on = \"中类\")\n",
    "#空值用无风险填充\n",
    "tx_df2[\"Mcc_bin\"]=tx_df2[\"Mcc_bin\"].fillna(\"无\")\n",
    "#获得每一个客户不同风险的交易笔数\n",
    "risk_rate = tx_df2.groupby(\"ID\")[\"Mcc_bin\"].value_counts().to_frame(name = \"count\").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#获取高|中|低风险交易笔数\n",
    "risk_H = pd.Series(risk_rate[risk_rate[\"Mcc_bin\"]==\"高\"][\"count\"].values,\n",
    "                   index = risk_rate[risk_rate[\"Mcc_bin\"]==\"高\"][\"ID\"],name = \"Hrisk_rate\")\n",
    "risk_M = pd.Series(risk_rate[risk_rate[\"Mcc_bin\"]==\"中\"][\"count\"].values,\n",
    "                   index = risk_rate[risk_rate[\"Mcc_bin\"]==\"中\"][\"ID\"],name = \"Mrisk_rate\")\n",
    "risk_L = pd.Series(risk_rate[risk_rate[\"Mcc_bin\"]==\"低\"][\"count\"].values,\n",
    "                   index = risk_rate[risk_rate[\"Mcc_bin\"]==\"低\"][\"ID\"],name = \"Lrisk_rate\")\n",
    "#获得所有交易笔数\n",
    "tx_cnt = tx_df2.groupby(\"ID\")[\"Mcc_bin\"].count()\n",
    "tx_cnt.name = \"total_tx_cnt\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#获得各个风险交易的占比\n",
    "risk_tx_info = pd.concat([ex_cnt,ex_amt,ex_cnt1,ex_amt1,tx_mon_cnt,risk_H,risk_M,risk_L,tx_cnt],axis=1)\n",
    "#空值用0填充\n",
    "risk_tx_info = risk_tx_info.fillna(0.0001)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ex_info = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#求得每个客户中不同风险的占比\n",
    "ex_info[\"Hrisk_rate\"] = risk_tx_info[\"Hrisk_rate\"]/risk_tx_info[\"total_tx_cnt\"]\n",
    "ex_info[\"Mrisk_rate\"] = risk_tx_info[\"Mrisk_rate\"]/risk_tx_info[\"total_tx_cnt\"]\n",
    "ex_info[\"Lrisk_rate\"] = risk_tx_info[\"Lrisk_rate\"]/risk_tx_info[\"total_tx_cnt\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Count</th>\n",
       "      <th>Amount</th>\n",
       "      <th>sCcount</th>\n",
       "      <th>sAmount</th>\n",
       "      <th>Tx_Mon</th>\n",
       "      <th>Hrisk_rate</th>\n",
       "      <th>Mrisk_rate</th>\n",
       "      <th>Lrisk_rate</th>\n",
       "      <th>total_tx_cnt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1.00</th>\n",
       "      <td>7.00</td>\n",
       "      <td>2912.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.00</th>\n",
       "      <td>11.00</td>\n",
       "      <td>5280.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.55</td>\n",
       "      <td>0.36</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6.00</th>\n",
       "      <td>6.00</td>\n",
       "      <td>10511.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.14</td>\n",
       "      <td>0.29</td>\n",
       "      <td>0.57</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7.00</th>\n",
       "      <td>12.00</td>\n",
       "      <td>25713.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.23</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8.00</th>\n",
       "      <td>4.00</td>\n",
       "      <td>1119.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.80</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Count   Amount  sCcount  sAmount  Tx_Mon  Hrisk_rate  Mrisk_rate  \\\n",
       "ID                                                                       \n",
       "1.00   7.00  2912.00     0.00     0.00       7        1.00        0.00   \n",
       "5.00  11.00  5280.00     0.00     0.00       5        0.09        0.55   \n",
       "6.00   6.00 10511.00     1.00     3.00       3        0.14        0.29   \n",
       "7.00  12.00 25713.00     1.00     5.00       3        0.15        0.62   \n",
       "8.00   4.00  1119.00     1.00     2.00       4        0.00        0.20   \n",
       "\n",
       "      Lrisk_rate  total_tx_cnt  \n",
       "ID                              \n",
       "1.00        0.00             7  \n",
       "5.00        0.36            11  \n",
       "6.00        0.57             7  \n",
       "7.00        0.23            13  \n",
       "8.00        0.80             5  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "risk_tx_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "#月均消费次数\n",
    "ex_info[\"XF_CNT_M\"] = risk_tx_info[\"Count\"]/risk_tx_info[\"Tx_Mon\"]\n",
    "#月均消费金额\n",
    "ex_info[\"XF_AMT_M\"] = risk_tx_info[\"Amount\"]/risk_tx_info[\"Tx_Mon\"]\n",
    "#月均入账次数\n",
    "ex_info[\"RZ_CNT_M\"] = risk_tx_info[\"sCcount\"]/risk_tx_info[\"Tx_Mon\"]\n",
    "#月均入账金额\n",
    "ex_info[\"RZ_AMT_M\"] = risk_tx_info[\"sAmount\"]/risk_tx_info[\"Tx_Mon\"]\n",
    "#笔均消费金额\n",
    "ex_info[\"XF_AMT_CNT\"] = risk_tx_info[\"Amount\"]/risk_tx_info[\"Count\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Hrisk_rate</th>\n",
       "      <th>Mrisk_rate</th>\n",
       "      <th>Lrisk_rate</th>\n",
       "      <th>XF_CNT_M</th>\n",
       "      <th>XF_AMT_M</th>\n",
       "      <th>RZ_CNT_M</th>\n",
       "      <th>RZ_AMT_M</th>\n",
       "      <th>XF_AMT_CNT</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1.00</th>\n",
       "      <td>0.14</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>416.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>416.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.00</th>\n",
       "      <td>0.01</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.03</td>\n",
       "      <td>2.20</td>\n",
       "      <td>1056.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>480.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6.00</th>\n",
       "      <td>0.02</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.08</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3503.67</td>\n",
       "      <td>0.33</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1751.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7.00</th>\n",
       "      <td>0.01</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.02</td>\n",
       "      <td>4.00</td>\n",
       "      <td>8571.00</td>\n",
       "      <td>0.33</td>\n",
       "      <td>1.67</td>\n",
       "      <td>2142.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8.00</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.16</td>\n",
       "      <td>1.00</td>\n",
       "      <td>279.75</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.50</td>\n",
       "      <td>279.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Hrisk_rate  Mrisk_rate  Lrisk_rate  XF_CNT_M  XF_AMT_M  RZ_CNT_M  \\\n",
       "ID                                                                       \n",
       "1.00        0.14        0.00        0.00      1.00    416.00      0.00   \n",
       "5.00        0.01        0.05        0.03      2.20   1056.00      0.00   \n",
       "6.00        0.02        0.04        0.08      2.00   3503.67      0.33   \n",
       "7.00        0.01        0.05        0.02      4.00   8571.00      0.33   \n",
       "8.00        0.00        0.04        0.16      1.00    279.75      0.25   \n",
       "\n",
       "      RZ_AMT_M  XF_AMT_CNT  \n",
       "ID                          \n",
       "1.00      0.00      416.00  \n",
       "5.00      0.00      480.00  \n",
       "6.00      1.00     1751.83  \n",
       "7.00      1.67     2142.75  \n",
       "8.00      0.50      279.75  "
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ex_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ID_NO_IDENT', 'LIMIT_BAL', 'SEX', 'EDUCATION', 'JOB_TYPE', 'MARRIAGE',\n",
       "       'ZIP_CODE', 'BIRTHDAY', 'AGE', 'CASE_NO', 'CASE_FROM', 'PAY_COND',\n",
       "       'PAY_1', 'PAY_2', 'PAY_3', 'PAY_4', 'PAY_5', 'PAY_6', 'BILL_AMT1',\n",
       "       'BILL_AMT2', 'BILL_AMT3', 'BILL_AMT4', 'BILL_AMT5', 'BILL_AMT6',\n",
       "       'PAY_AMT1', 'PAY_AMT2', 'PAY_AMT3', 'PAY_AMT4', 'PAY_AMT5', 'PAY_AMT6',\n",
       "       'SCORING', 'CUST_TYPE', 'LIMIT_RATE_1', 'LIMIT_RATE_2', 'LIMIT_RATE_3',\n",
       "       'LIMIT_RATE_4', 'LIMIT_RATE_5', 'LIMIT_RATE_6', 'REPAY_RATE_1',\n",
       "       'REPAY_RATE_2', 'REPAY_RATE_3', 'REPAY_RATE_4', 'REPAY_RATE_5',\n",
       "       'REPAY_RATE_6', 'ALL_MON', 'OVERDUE_CNT_3', 'OVERDUE_MAX_MON',\n",
       "       'USE_MON'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trainset.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "trainset1 = trainset[['ID_NO_IDENT', 'LIMIT_BAL', 'SEX', 'EDUCATION', 'JOB_TYPE', 'MARRIAGE',\n",
    "       'ZIP_CODE',  'AGE',  'CASE_FROM',\n",
    "       'PAY_1', 'PAY_2', 'PAY_3', 'PAY_4', 'PAY_5', 'PAY_6', 'BILL_AMT1',\n",
    "       'BILL_AMT2', 'BILL_AMT3', 'BILL_AMT4', 'BILL_AMT5', 'BILL_AMT6',\n",
    "       'PAY_AMT1', 'PAY_AMT2', 'PAY_AMT3', 'PAY_AMT4', 'PAY_AMT5', 'PAY_AMT6',\n",
    "       'CUST_TYPE', 'LIMIT_RATE_1', 'LIMIT_RATE_2', 'LIMIT_RATE_3',\n",
    "       'LIMIT_RATE_4', 'LIMIT_RATE_5', 'LIMIT_RATE_6', 'REPAY_RATE_1',\n",
    "       'REPAY_RATE_2', 'REPAY_RATE_3', 'REPAY_RATE_4', 'REPAY_RATE_5',\n",
    "       'REPAY_RATE_6', 'ALL_MON', 'OVERDUE_CNT_3', 'OVERDUE_MAX_MON',\n",
    "       'USE_MON']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = pd.merge(trainset1,ex_info,how = \"left\",left_on = \"ID_NO_IDENT\",right_index =True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = train.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID_NO_IDENT        0\n",
       "LIMIT_BAL          0\n",
       "SEX                0\n",
       "EDUCATION          0\n",
       "JOB_TYPE           0\n",
       "MARRIAGE           0\n",
       "ZIP_CODE           0\n",
       "AGE                0\n",
       "CASE_FROM          0\n",
       "PAY_1              0\n",
       "PAY_2              0\n",
       "PAY_3              0\n",
       "PAY_4              0\n",
       "PAY_5              0\n",
       "PAY_6              0\n",
       "BILL_AMT1          0\n",
       "BILL_AMT2          0\n",
       "BILL_AMT3          0\n",
       "BILL_AMT4          0\n",
       "BILL_AMT5          0\n",
       "BILL_AMT6          0\n",
       "PAY_AMT1           0\n",
       "PAY_AMT2           0\n",
       "PAY_AMT3           0\n",
       "PAY_AMT4           0\n",
       "PAY_AMT5           0\n",
       "PAY_AMT6           0\n",
       "SCORING            0\n",
       "CUST_TYPE          0\n",
       "LIMIT_RATE_1       0\n",
       "LIMIT_RATE_2       0\n",
       "LIMIT_RATE_3       0\n",
       "LIMIT_RATE_4       0\n",
       "LIMIT_RATE_5       0\n",
       "LIMIT_RATE_6       0\n",
       "REPAY_RATE_1       0\n",
       "REPAY_RATE_2       0\n",
       "REPAY_RATE_3       0\n",
       "REPAY_RATE_4       0\n",
       "REPAY_RATE_5       0\n",
       "REPAY_RATE_6       0\n",
       "ALL_MON            0\n",
       "OVERDUE_CNT_3      0\n",
       "OVERDUE_MAX_MON    0\n",
       "USE_MON            0\n",
       "Hrisk_rate         0\n",
       "Mrisk_rate         0\n",
       "Lrisk_rate         0\n",
       "XF_CNT_M           0\n",
       "XF_AMT_M           0\n",
       "RZ_CNT_M           0\n",
       "RZ_AMT_M           0\n",
       "XF_AMT_CNT         0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(62309, 53)"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.to_csv(\"traindata.csv\",index = True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
